﻿
DROP FUNCTION IF EXISTS inserirBairro(varchar(40));
DROP FUNCTION IF EXISTS inserirColetor(varchar(40));
DROP FUNCTION IF EXISTS inserirEspecie(varchar(40));
DROP FUNCTION IF EXISTS inserirRua(dataColetaRua date,nomeRua varchar(40),nomeBairro varchar(40));
DROP FUNCTION IF EXISTS inserirRuaColetor(varchar(40),varchar(9),date,varchar(40),integer);
DROP FUNCTION IF EXISTS inserirArvore(
	numeroOrdemArv	integer,
	raioCopaArv	real,
	numeroProxArv	integer,
	posicaoArv	char,
	alturaArv	char,
	alturaBifArv	char,
	plantioArv	char,
	danoArv		char,
	situacaoArv	char,
	matrizArv	char,
	nomeRua		varchar(40),
	dataColeta	date,
	nomeBairro	varchar(40),
	nomeColetor	varchar(40),
	nomeEspecie 	varchar(40)
	);

-- funcao inserir bairro --
CREATE or REPLACE FUNCTION inserirBairro(nomeBairro varchar(40)) RETURNS integer AS 
$$
DECLARE
	nomeBairro ALIAS FOR $1;
	id_bairro integer;
BEGIN
	id_bairro := (SELECT bairroId FROM bairro where nome_bairro = nomeBairro);

	IF (SELECT bairroId FROM bairro where nome_bairro = nomeBairro) > 0 THEN
		RETURN id_bairro;
	ELSE
		insert into bairro(nome_bairro) values (nomeBairro);
		id_bairro := (SELECT bairroId FROM bairro where nome_bairro = nomeBairro);
	END IF;

	RETURN id_bairro;
END
$$
LANGUAGE plpgsql;

-- exemplo de utilizacao --
-- select inserirBairro('fabio'); --

-- funcao inserir coletor --
CREATE or REPLACE FUNCTION inserirColetor(nomeColetor varchar(40)) RETURNS integer AS 
$$
DECLARE
	nomeColetor ALIAS FOR $1;
	id_coletor integer;
BEGIN
	id_coletor := (SELECT coletorId FROM coletor where nome_coletor = nomeColetor);

	IF (SELECT coletorId FROM coletor where nome_coletor = nomeColetor) > 0 THEN
		RETURN id_coletor;
	ELSE
		insert into coletor(nome_coletor) values (nomeColetor);
		id_coletor := (SELECT coletorId FROM coletor where nome_coletor = nomeColetor);
	END IF;

	RETURN id_coletor;
END
$$
LANGUAGE plpgsql;

-- exemplo de utilizacao --
-- select inserirColetor('fabio'); --

-- funcao inserir genero --
CREATE or REPLACE FUNCTION inserirGenero(nomeGenero varchar(30)) RETURNS integer AS 
$$
DECLARE
	nomeGenero ALIAS FOR $1;
	id_genero integer;
BEGIN
	id_genero := (SELECT generoId FROM genero where nome_genero = nomeGenero);

	IF (SELECT generoId FROM genero where nome_genero = nomeGenero) > 0 THEN
		RETURN id_genero;
	ELSE
		insert into genero(nome_genero) values (nomeGenero);
		id_genero := (SELECT generoId FROM genero where nome_genero = nomeGenero);
	END IF;
	
	RETURN id_genero;
END
$$
LANGUAGE plpgsql;

-- exemplo de utilizacao --
-- select inserirGenero('genero'); --

-- funcao inserir especie --
CREATE or REPLACE FUNCTION inserirEspecie(
	nomeCientifico varchar(50),
	nomePopular varchar(50),
	localOrigem char,
	foto varchar(20),
	nomeGenero varchar(30)
	) RETURNS integer AS 
$$
DECLARE
	nomeCientifico	ALIAS FOR $1;
	nomePopular 	ALIAS FOR $2;
	localOrigem 	ALIAS FOR $3;
	enderecoFoto	ALIAS FOR $4;
	nomeGenero 	ALIAS FOR $5;
	id_especie 	integer;
	id_novo_genero	integer;
BEGIN
	id_especie := (SELECT especieId FROM especie where nome_cientifico = nomeCientifico);

	id_novo_genero := inserirGenero(nomeGenero);

	IF (SELECT especieId FROM especie where nome_cientifico = nomeCientifico) > 0 THEN
		RETURN id_especie;
	ELSE
		insert into especie(
			nome_cientifico,
			nome_popular,
			origem,
			foto,
			id_genero
		) values (
			nomeCientifico,
			nomePopular,
			localOrigem,
			enderecoFoto,
			id_novo_genero);
		id_especie := (SELECT especieId FROM especie where nome_cientifico = nomeCientifico);
	END IF;
	
	RETURN id_especie;
END
$$
LANGUAGE plpgsql;

-- exemplo de utilizacao --
-- select inserirEspecie('cientifico','popular','origem','foto','genero'); --

-- funcao inserir rua --
CREATE or REPLACE FUNCTION inserirRua(
	nomeRua varchar(60),
	nomeBairro varchar(40)
	) RETURNS integer AS 
$$
DECLARE
	nomeRua ALIAS FOR $1;
	nomeBairro ALIAS FOR $2;
	id_nova_rua integer;
	id_novo_bairro integer;
BEGIN
	id_nova_rua := (SELECT ruaId FROM rua where nome_rua = nomeRua);
	id_novo_bairro := inserirBairro(nomeBairro);
	
	IF (SELECT ruaId FROM rua where nome_rua = nomeRua) > 0 THEN
		RETURN id_nova_rua;
	ELSE
		insert into rua(nome_rua,id_bairro) values (nomeRua,id_novo_bairro);
		id_nova_rua := (SELECT ruaId FROM rua where nome_rua = nomeRua);
	END IF;
	
	RETURN id_nova_rua;
END
$$
LANGUAGE plpgsql;

-- exemplo de utilizacao --
-- select inserirRua('rua fabio almeida','nome bairro') --

-- funcao inserir relacao coletor x rua --
CREATE or REPLACE FUNCTION inserirRuaColetor(
	nomeColetor varchar(40),
	nomeRua varchar(60),
	nomeBairro varchar(40)
	) RETURNS integer AS 
$$
DECLARE
	nomeColetor ALIAS FOR $1;
	nomeRua ALIAS FOR $2;
	nomeBairro ALIAS FOR $3;
	id_nova_rua integer;
	id_novo_coletor integer;
BEGIN
	id_nova_rua := inserirRua(nomeRua,nomeBairro);
	id_novo_coletor := inserirColetor(nomeColetor);

	IF (SELECT id_rua FROM tr_rua_coletor where id_rua = id_nova_rua) > 0 THEN
		RETURN id_nova_rua;
	ELSE
		INSERT INTO tr_rua_coletor (id_rua,id_coletor) values (id_nova_rua,id_novo_coletor);
	END IF;
	
	RETURN id_nova_rua;
END
$$
LANGUAGE plpgsql;

-- exemplo de utilizacao --
-- select inserirRuaColetor('equipe 1','RUA','bairro') --

-- funcao inserir arvore --
CREATE or REPLACE FUNCTION inserirArvore(
	dataColeta	date,
	numeroOrdemArv	integer,
	raioCopaArv	real,
	areaCopaArv	real,
	numeroProxArv	integer,
	posicaoArv	char,
	alturaArv	char,
	alturaBifArv	char,
	plantioArv	char,
	danoArv		char,
	situacaoArv	char,
	matrizArv	char,
	nomeRua		varchar(60),
	nomeBairro	varchar(40),
	nomeColetor	varchar(40),
	nomeGenero	varchar(30),
	nomeCientifico 	varchar(50),
	nomePopular	varchar(50),
	localOrigem	char,
	enderecoFoto	varchar(20)
	) RETURNS integer AS 
$$
DECLARE
	data_coleta	ALIAS FOR $1;
	numeroOrdemArv 	ALIAS FOR $2;
	raioCopaArv 	ALIAS FOR $3;
	areaCopaArv	ALIAS FOR $4;
	numeroProxArv 	ALIAS FOR $5;
	posicaoArv	ALIAS FOR $6;
	alturaArv	ALIAS FOR $7;
	alturaBifArv	ALIAS FOR $8;
	plantioArv	ALIAS FOR $9;
	danoArv		ALIAS FOR $10;
	situacaoArv	ALIAS FOR $11;
	matrizArv	ALIAS FOR $12;
	nomeRua 	ALIAS FOR $13;
	nomeBairro	ALIAS FOR $14;
	nomeColetor	ALIAS FOR $15;
	nomeGenero	ALIAS FOR $16;
	nomeCientifico	ALIAS FOR $17;
	nomePopular	ALIAS FOR $18;
	localOrigem	ALIAS FOR $19;
	enderecoFoto	ALIAS FOR $20;
	
	id_nova_rua	integer;
	id_nova_especie	integer;
	id_nova_arvore 	integer;
BEGIN
	id_nova_rua := inserirRuaColetor(nomeColetor,nomeRua,nomeBairro);
	id_nova_especie := inserirEspecie(nomeCientifico,nomePopular,localOrigem,enderecoFoto,nomeGenero);

	INSERT INTO arvore (
		dataColeta,
		numeroOrdem,
		raioCopa,
		areaCopa,
		numeroProximo,
		posicao,
		altura,
		alturaBif,
		plantio,
		dano,
		situacao,
		matriz,
		id_rua,
		id_especie
	) values (
		data_coleta,
		numeroOrdemArv,
		raioCopaArv,
		areaCopaArv,
		numeroProxArv,
		posicaoArv,
		alturaArv,
		alturaBifArv,
		plantioArv,
		danoArv,
		situacaoArv,
		matrizArv,
		id_nova_rua,
		id_nova_especie
	);

	id_nova_arvore := (SELECT MAX(arvoreId) FROM arvore);
	
	RETURN id_nova_arvore;
END
$$
LANGUAGE plpgsql;

-- exemplo de utilizacao --
select inserirArvore(
	'1/1/21',	--dataColeta		--date,
	'1',		--numeroOrdemArv	--integer,
	'2.0',		--raioCopaArv		--real,
	'5.6',		--areaCopaArv		--real,
	'12',		--numeroProxArv		--integer,
	'A',		--posicaoArv		--char,
	'A',		--alturaArv		--char,
	'A',		--alturaBifArv		--char,
	'A',		--plantioArv		--char,
	'A',		--danoArv		--char,
	'A',		--situacaoArv		--char,
	'A',		--matrizArv		--char,
	'RUA',		--nomeRua		--varchar(60),
	'BAIRRO',	--nomeBairro		--varchar(40),
	'COLETOR',	--nomeColetor		--varchar(40),
	'GENERO',	--nomeGenero		--varchar(30),
	'CIENTIFICO',	--nomeCientifico 	--varchar(50),
	'POPULAR',	--nomePopular		--varchar(50),
	'A',		--localOrigem		--char,
	'FOTO'		--enderecoFoto		--varchar(20)
	);


-- funcao inserir arvore amostragem --
CREATE or REPLACE FUNCTION inserirArvoreAmostragem(
	dataColeta	date,
	numeroOrdemArv	integer,
	raioCopaArv	real,
	areaCopaArv	real,
	numeroProxArv	integer,
	posicaoArv	char,
	alturaArv	char,
	alturaBifArv	char,
	plantioArv	char,
	danoArv		char,
	situacaoArv	char,
	matrizArv	char,
	nomeRua		varchar(60),
	nomeBairro	varchar(40),
	nomeColetor	varchar(40),
	nomeGenero	varchar(30),
	nomeCientifico 	varchar(50),
	nomePopular	varchar(50),
	localOrigem	char,
	enderecoFoto	varchar(20),
-- amostragem --
	larguraRua		real,
	larguraCalcada		real,
	afastPredial		real,
	ocupacaoBairro		char,
	Caminhamento		char,
	Diametro		char,
	tipoPoda		char,
	qualidadeCopa		char,
	qualidadeTronco		char,
	inclinacaoTronco	char,
	Fitossanidade		char,
	Cabos			char,
	Silvicultura		char
	) RETURNS integer AS 
$$
DECLARE
	data_coleta	ALIAS FOR $1;
	numeroOrdemArv 	ALIAS FOR $2;
	raioCopaArv 	ALIAS FOR $3;
	areaCopaArv	ALIAS FOR $4;
	numeroProxArv 	ALIAS FOR $5;
	posicaoArv	ALIAS FOR $6;
	alturaArv	ALIAS FOR $7;
	alturaBifArv	ALIAS FOR $8;
	plantioArv	ALIAS FOR $9;
	danoArv		ALIAS FOR $10;
	situacaoArv	ALIAS FOR $11;
	matrizArv	ALIAS FOR $12;
	nomeRua 	ALIAS FOR $13;
	nomeBairro	ALIAS FOR $14;
	nomeColetor	ALIAS FOR $15;
	nomeGenero	ALIAS FOR $16;
	nomeCientifico	ALIAS FOR $17;
	nomePopular	ALIAS FOR $18;
	localOrigem	ALIAS FOR $19;
	enderecoFoto	ALIAS FOR $20;
-- amostragem --
	larguraRua		ALIAS FOR $21;
	larguraCalcada		ALIAS FOR $22;
	afastPredial		ALIAS FOR $23;
	ocupacaoBairro		ALIAS FOR $24;
	Caminhamento		ALIAS FOR $25;
	Diametro		ALIAS FOR $26;
	tipoPoda		ALIAS FOR $27;
	qualidadeCopa		ALIAS FOR $28;
	qualidadeTronco		ALIAS FOR $29;
	inclinacaoTronco	ALIAS FOR $30;
	Fitossanidade		ALIAS FOR $31;
	Cabos			ALIAS FOR $32;
	Silvicultura		ALIAS FOR $33;

	id_nova_arvore 	integer;
BEGIN
	id_nova_arvore := inserirArvore(
		dataColeta,
		numeroOrdemArv,
		raioCopaArv,
		areaCopaArv,
		numeroProxArv,
		posicaoArv,
		alturaArv,
		alturaBifArv,
		plantioArv,
		danoArv,
		situacaoArv,
		matrizArv,
		nomeRua,
		nomeBairro,
		nomeColetor,
		nomeGenero,
		nomeCientifico,
		nomePopular,
		localOrigem,
		enderecoFoto
	);
	
	INSERT INTO amostragem (
		id_arvore,
		largura_rua,
		largura_calcada,
		afast_predial,
		ocupacao_bairro,
		caminhamento,
		diametro,
		tipo_poda,
		qualidade_copa,
		qualidade_tronco,
		inclinacao_tronco,
		fitossanidade,
		cabos,
		silvicultura
	) values (
		id_nova_arvore,
		larguraRua,
		larguraCalcada,
		afastPredial,
		ocupacaoBairro,
		Caminhamento,
		Diametro,
		tipoPoda,
		qualidadeCopa,
		qualidadeTronco,
		inclinacaoTronco,
		Fitossanidade,
		Cabos,
		Silvicultura
	);
	
	RETURN id_nova_arvore;
END
$$
LANGUAGE plpgsql;
